package com.corpdata.app.customer.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import com.corpdata.app.customer.entity.CustomerInfo;
import com.corpdata.core.base.BaseMapper;
import com.github.pagehelper.Page;

/**
 * 客户表 持久层接口
 * @author wu
 * @email 64088839@qq.com
 * @date 2018-06-13 16:46:46
 */
public interface CustomerInfoMapper extends BaseMapper<CustomerInfo>{

	/**
	 * 自定义的扩展查询
	 * @param params
	 * @return
	 */
	@Select("<script>select a.id,a.customer_name,a.customer_source,d.customer_state,b.User_Name,c.Project_name,a.created,d.created modified " + 
			"from customer_info a  " + 
			"LEFT JOIN org_user b on a.executor=b.id " + 
			"inner join sell_record d on d.fk_customer=a.id and d.on_use=1 "+
			"LEFT JOIN project_info c on d.Win_projectid=c.id  " +
			" where 1=1 " +
			"<if test='projectId!=null'> " +
			"and d.Customer_state=1 and d.Win_projectid in <foreach collection=\"projectId\" item=\"t\" open=\"(\" separator=\",\" close=\")\" >#{t}</foreach> "+
			"</if>" +
			"<if test='executor != null' >and a.executor=#{executor}</if> "+
			"<if test='customerStatus != null' >and d.customer_State=#{customerStatus}</if> "+
			"<if test='customerSource != null' >and a.customer_Source=#{customerSource}</if> "+
			"<if test='customerName != null' >and a.customer_name like '%${customerName}%'</if> "+
			"</script>")
	@Results(id="extendResult",value= {
			@Result(id=true,property="id",column="id",javaType=String.class),
			@Result(property="customerName",column="customer_name",javaType=String.class),
			@Result(property="customerSource",column="customer_source",javaType=Integer.class),
			@Result(property="customerState",column="customer_state",javaType=Integer.class),
			@Result(property="executor",column="User_Name",javaType=String.class),
			@Result(property="winProjectid",column="Project_name",javaType=String.class),
			@Result(property="created",column="created",javaType=Date.class),
			@Result(property="modified",column="modified",javaType=Date.class)
	})
	Page<CustomerInfo> selectAllExt(Map<String, Object> params);
	
	@Select("select a.*,b.Fk_user from customer_info a " + 
			"LEFT join sell_team_staff b on a.id=b.Fk_customer_info " +
			"where a.id=#{id}")
	@ResultMap("extendResultMap")
	CustomerInfo selectEdit(String id);

	@Select("<script>select count(1) from customer_info where "+
			"<if test='date.length()==7'> DATE_FORMAT(created,'%Y-%m') =#{date} </if>"+
			"<if test='date.length()==6'> DATE_FORMAT(created,'%Y%u') = #{date} </if> </script>")
	int findCountByDate(@Param("date")String date);
	
	/**
	 * 查询客户首页跟进信息
	 * 查询销售记录状态为初步接洽 customerState=0 and on_use=1 
	 * 如果下次跟进时间超过当前时间则为逾期态
	 * @return
	 */
	@Select("select b.id,c.User_Name executor,a.Next_tim_date nextTimDate, "+
			"case when a.Next_tim_date<curdate() then 1 else 0 end state "+ //1:逾期; 0:跟进;
			"from sell_record a  " + 
			"inner join customer_info b on a.Fk_customer=b.id " + 
			"inner join org_user c on c.id=b.Executor " + 
			"where a.on_use=1 and a.Customer_state=0")
	List<Map<String,Object>> selectFollow();

	/**
	 * 跟进客户名称和手机号 模糊搜索客户
	 * @param param
	 * @return
	 */
	@Select("<script>select a.id,a.Customer_name customerName,c.User_Name executor,b.Customer_state customerState,a.Created created,b.Next_tim_date nextTimDate,a.Contacts_telephone contactsTelephone " + 
			"from customer_info a " + 
			"inner join sell_record b on b.on_use=1 and a.id=b.Fk_customer " + 
			"left join org_user c on c.id=a.Executor " + 
			"<where> "+
			"<if test='param!=null' >"+
			"a.Customer_name like '${param}%' "+
			// "or a.Agent_telephone like '${param}%' "+ //代理人先不用  使用联系人搜索
			"or a.Contacts_telephone like '${param}%' "+
			"</if>"+
			"</where></script>")
	List<Map<String, Object>> selectCusByNamePhone(@Param("param")String param);
}
